# -*- coding: utf-8 -*-
# =============================================================================
#     FileName:
#         Desc:
#       Author: GGA
#        Email:
#     HomePage:
#      Version: 1.0.1
#   LastChange: 2020-12-20
#      History:
# =============================================================================
import json
import time
import datetime
import decimal
import pymysql
from utils.logger_helper import LoggerHelper
from utils.json_helper import ExJsonEncoder

logger = LoggerHelper.get_logger("mysql_server")


class MySQLServer(object):
    def __init__(self, mysql_host="127.0.0.1", mysql_port=3306, mysql_user="root", mysql_password="",
                 database_name="mysql", default_charset="utf8mb4", connection_timeout=60, read_timeout=60):
        self.mysql_host = mysql_host
        self.mysql_port = mysql_port
        self.mysql_user = mysql_user
        self.mysql_password = mysql_password
        self.database_name = database_name
        self.default_charset = default_charset
        self.auto_commit = True
        self.connection_timeout = connection_timeout
        self.read_timeout = read_timeout

    class MySQLJsonEncoder(json.JSONEncoder):
        def default(self, obj):
            if obj is None:
                return 'None'
            if isinstance(obj, datetime.datetime):
                return obj.strftime('%Y-%m-%d %H:%M:%S')
            elif isinstance(obj, datetime.date):
                return obj.strftime('%Y-%m-%d')
            elif isinstance(obj, datetime.time):
                return obj.strftime('%H:%M:%S')
            elif isinstance(obj, decimal.Decimal):
                return str(obj)
            else:
                return json.JSONEncoder.default(self, obj)

    def get_server_connection(self):
        """
        根据数据库配置获取数据库连接
        """
        return pymysql.connect(
            host=self.mysql_host,
            port=self.mysql_port,
            user=self.mysql_user,
            password=self.mysql_password,
            autocommit=self.auto_commit,
            read_timeout=self.read_timeout,
            connect_timeout=self.connection_timeout,
            charset=self.default_charset,
            db=self.database_name,
        )

    @classmethod
    def _close_connection(cls, mysql_cursor=None, mysql_conn=None):
        if mysql_cursor is not None:
            try:
                mysql_cursor.close()
            except Exception as _:
                pass
        if mysql_conn is not None:
            try:
                mysql_conn.close()
            except Exception as _:
                pass
        pass

    def mysql_query(self, sql_script, sql_paras=None, return_dict=True):
        """
        执行MySQL查询并返回查询结果，如果异常，返回None
        """
        mysql_cursor = None
        mysql_conn = None
        try:
            mysql_conn = self.get_server_connection()
            if return_dict:
                mysql_cursor = mysql_conn.cursor(pymysql.cursors.DictCursor)
            else:
                mysql_cursor = mysql_conn.cursor()
            if sql_paras is not None:
                mysql_cursor.execute(sql_script, sql_paras)
            else:
                mysql_cursor.execute(sql_script)
            exec_result = mysql_cursor.fetchall()
            return exec_result
        except Exception as ex:
            logger.error("对实例{}:{}请求出现异常,执行SQL:\n{}\n执行参数：\n:{}\n异常信息：\n{}".format(
                self.mysql_host, self.mysql_port, sql_script, json.dumps(sql_paras, cls=ExJsonEncoder), str(ex)
            ))
            raise ex
        finally:
            self._close_connection(mysql_cursor=mysql_cursor, mysql_conn=mysql_conn)

    def mysql_exec(self, sql_script, sql_paras=None):
        """
        执行MYSQL语句并返回影响行数，如果异常则返回None
        """
        mysql_cursor = None
        mysql_conn = None
        try:
            mysql_conn = self.get_server_connection()
            mysql_cursor = mysql_conn.cursor()
            if sql_paras is not None:
                mysql_cursor.execute(sql_script, sql_paras)
            else:
                mysql_cursor.execute(sql_script)
            affect_rows = mysql_cursor.rowcount
            mysql_conn.commit()
            return affect_rows
        except Exception as ex:
            logger.error("对实例{}:{}请求出现异常,执行SQL:\n{}\n执行参数：\n:{}\n异常信息：\n{}".format(
                self.mysql_host, self.mysql_port, sql_script, json.dumps(sql_paras, cls=ExJsonEncoder), str(ex)
            ))
            raise ex
        finally:
            self._close_connection(mysql_cursor=mysql_cursor, mysql_conn=mysql_conn)

    def mysql_exec_many(self, sql_items: list):
        """
        执行MYSQL语句并返回影响行数，如果异常则返回None
        """
        mysql_cursor = None
        mysql_conn = None
        sql_script = ""
        sql_paras = None
        try:
            mysql_conn = self.get_server_connection()
            mysql_cursor = mysql_conn.cursor()
            affect_rows_list = list()
            for sql_script, sql_paras in sql_items:
                if sql_paras is not None:
                    mysql_cursor.execute(sql_script, sql_paras)
                else:
                    mysql_cursor.execute(sql_script)
                affect_rows = mysql_cursor.rowcount
                affect_rows_list.append(affect_rows)
            mysql_conn.commit()
            return affect_rows_list
        except Exception as ex:
            logger.error("对实例{}:{}请求出现异常,执行SQL:\n{}\n执行参数：\n:{}\n异常信息：\n{}".format(
                self.mysql_host, self.mysql_port, sql_script, json.dumps(sql_paras, cls=MySQLJsonEncoder), str(ex)
            ))
            raise ex
        finally:
            self._close_connection(mysql_cursor=mysql_cursor, mysql_conn=mysql_conn)


def test():
    server_config = {
        "mysql_host": "192.168.12.72",
        "mysql_port": 3306,
        "mysql_user": "root",
        "mysql_password": "root@123",
        "database_name": "test"
    }
    mysql_server = MySQLServer(**server_config)
    query_result = mysql_server.mysql_query(
        sql_script="SELECT * FROM redis_info.redis_8182 where key_name=%s limit 10;",
        sql_paras=["STATIC_SE_'"]
    )
    print(query_result)


if __name__ == '__main__':
    test()
